﻿using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace SY_Anekay_LMJ_OutPutExcelReport
{
    public class FillProdutivitySheetTool
    {
        public DataSet DataSet { get; set; }
        public Workbook Workbook { get; set; }

        public FillProdutivitySheetTool(Workbook workbook,DataSet ds)
        {
            Workbook = workbook;
            DataSet = ds;
        }

        public void FillSheet(int monthStart,int monthEnd,int year)
        {
            List<Cell> titles = new List<Cell>();
            List<Cell> entryTitles = new List<Cell>();
            List<Cell> entryContents = new List<Cell>();
            List<Cell> gatherCell = new List<Cell>();
                       

            var table = DataSet.Tables["V_SY_Productivity"];
            var data = table.AsEnumerable().Where(p => p.Field<int>("Year") == year && p.Field<int>("Month") >= monthStart && p.Field<int>("Month") <= monthEnd);
            
            //测试数量统计
            var dataTest = data.Where(p => p.Field<string>("TestType") == "1" || p.Field<string>("TestType") == "2");
            var dataTestGrouped=dataTest.GroupBy(p => new { customer = p.Field<string>("Customer"), month = p.Field<int>("Month") }).
                Select(p => new { customer = p.Key.customer, month = p.Key.month, qty = p.Sum(q => q.Field<decimal>("Qty")) });

            //实际上机产能统计
            var dataTotal = data;
            var dataTotalGrouped = data.GroupBy(p => new { customer = p.Field<string>("Customer"), month = p.Field<int>("Month") }).
                Select(p => new { customer = p.Key.customer, month = p.Key.month, qty = p.Sum(q => q.Field<decimal>("Qty")) });

            //返工重测统计
            var dataOther = data.Where(p => p.Field<string>("ExamineType") == "3" || p.Field<string>("ExamineType") == "4");
            var dataOtherGrouped = dataOther.GroupBy(p => new
            {
                month = p.Field<int>("Month"),
                testTimes = p.Field<string>("TestType"),
                materialType = p.Field<string>("ExamineType")
            }).Select(p => new { month = p.Key.month, testTimes = p.Key.testTimes, materialType = (ExamineType)Convert.ToInt16(p.Key.materialType), qty = p.Sum(q => q.Field<decimal>("Qty")) });

            int entryTitleStartX = 1;
            int entryTitleStartY = 2;
            int entryTitleX = entryTitleStartX;
            int entryTitleY = entryTitleStartY;
            int entryStartX = 1;
            int entryStartY = 3;
            int entryX=entryStartX;
            int entryY=entryStartY;

            for (int i = monthStart; i <= monthEnd; i++)
            {
                Worksheet worksheet = new Worksheet();
                worksheet = (Worksheet)Workbook.Worksheets.Add(Missing.Value, Workbook.Worksheets[i-monthStart+1], 1);
                worksheet.Name = $"{monthStart}月产能统计表";


                entryTitles.Add(new Cell(entryTitleX, entryTitleY, "客户", true));
                entryTitles.Add(new Cell(entryTitleX, entryTitleY + 1, "测试数量", true));
                entryTitles.Add(new Cell(entryTitleX, entryTitleY + 2, "所占比例", true));
                entryTitleX += 1;
                var customers = dataTestGrouped.Select(p => p.customer).Distinct();
                var lastX = entryTitleStartX += customers.Count();
                foreach (var customer in customers)
                {
                    entryTitles.Add(new Cell(entryTitleX, entryTitleY, customer, true));
                    entryX = entryTitleX;
                    entryY = entryTitleY + 1;
                    entryContents.Add(new Cell(entryX, entryY , dataTestGrouped.Where(p => p.month == i && p.customer == customer).Select(p => p.qty).FirstOrDefault().ToString()));
                    entryContents.Add(new Cell(entryX, entryY + 1, $"={ Convert.ToChar(entryX + 64)}{entryY }/{Convert.ToChar(lastX + 64 + 1)}{entryY }") { NumberFormat="0.00%"});
                    entryTitleX += 1;
                }
                entryTitles.Add(new Cell(entryTitleX, entryTitleY, "总产能", true));
                entryContents.Add(new Cell(entryTitleX, entryY, $"=SUM({Convert.ToChar(entryStartX + 1 + 64)}{entryY}:{Convert.ToChar(lastX + 64)}{entryY})"));
                entryContents.Add(new Cell(entryTitleX, entryY + 1, $"100%"));
                titles.Add(new Cell(entryStartX, entryTitleStartY-1, $"{monthStart}月内订单测试数量统计表", true) { X2 = entryTitleX });

                entryTitleX += 2;
                entryStartX = entryTitleX;
                entryTitles.Add(new Cell(entryTitleX, entryTitleY, "客户", true));
                entryTitles.Add(new Cell(entryTitleX, entryTitleY + 1, "测试数量", true));
                entryTitles.Add(new Cell(entryTitleX, entryTitleY + 2, "所占比例", true));
                lastX = entryTitleX += customers.Count();
                foreach (var customer in customers)
                {
                    entryTitles.Add(new Cell(entryTitleX, entryTitleY, customer, true));
                    entryX = entryTitleX;
                    entryY = entryTitleY + 1;
                    entryContents.Add(new Cell(entryX, entryY, dataTotalGrouped.Where(p => p.month == i && p.customer == customer).Select(p => p.qty).FirstOrDefault().ToString()));
                    entryContents.Add(new Cell(entryX, entryY + 1, $"={ Convert.ToChar(entryX + 64)}{entryY }/{Convert.ToChar(lastX + 64 + 1)}{entryY  }") { NumberFormat = "0.00%" });
                    entryTitleX += 1;
                }
                entryTitles.Add(new Cell(entryTitleX, entryTitleY, "总产能", true));
                entryContents.Add(new Cell(entryTitleX, entryY, $"=SUM({Convert.ToChar(entryStartX + 1 + 64)}{entryY}:{Convert.ToChar(lastX + 64)}{entryY})"));
                entryContents.Add(new Cell(entryTitleX, entryY + 1, $"100%"));
                titles.Add(new Cell(entryStartX, entryTitleStartY-1, $"测试部{monthStart}月内实际上机产能统计表", true) { X2 = entryTitleX });

                entryTitleY = entryY + 3;
                entryTitleStartY = entryTitleY;
                entryTitleX = entryStartX;                
                entryX = entryStartX;
                entryY = entryTitleStartY + 3;

                entryTitleY += 1;
                entryTitles.Add(new Cell(entryTitleX, entryTitleY, "重测部分所占比例和数量") { Y2 = entryTitleY + 1, IsMerge = true });
                entryContents.Add(new Cell(entryX, entryY, "数量(PCS)"));
                entryContents.Add(new Cell(entryX, entryY+1, "比例(%)"));
                entryTitleX += 1;
                entryX += 1;
                entryTitles.Add(new Cell(entryTitleX, entryTitleY, "重测数量") { X2 = entryTitleX + 2 ,IsMerge=true});                
                entryTitles.Add(new Cell(entryTitleX, entryTitleY + 1, "重测有容量"));
                entryContents.Add(new Cell(entryX, entryY, 
                    dataOtherGrouped.Where(p => p.month == i && p.testTimes == "3" && p.materialType == ExamineType.Capacity).Select(p => p.qty).FirstOrDefault().ToString()));
                entryContents.Add(new Cell(entryX, entryY+1, $"={Convert.ToChar(entryX + 64)}{entryY}/{Convert.ToChar(entryStartX + 64 + 6)}{entryY}"));
                entryTitleX += 1;
                entryX += 1;
                entryTitles.Add(new Cell(entryTitleX, entryTitleY + 1, "重测有ID"));
                entryContents.Add(new Cell(entryX, entryY,
                    dataOtherGrouped.Where(p => p.month == i && p.testTimes == "3" && p.materialType == ExamineType.ID).Select(p => p.qty).FirstOrDefault().ToString()));
                entryContents.Add(new Cell(entryX, entryY+1, $"={Convert.ToChar(entryX + 64)}{entryY}/{Convert.ToChar(entryStartX + 64 + 6)}{entryY}"));
                entryX += 1;
                entryTitleX += 1;
                entryTitles.Add(new Cell(entryTitleX, entryTitleY + 1, "重测分BIN"));
                entryContents.Add(new Cell(entryX, entryY,
                    dataOtherGrouped.Where(p => p.month == i && p.testTimes == "3" && (p.materialType == ExamineType.BIN|| p.materialType == ExamineType.CapacitytBIN))
                    .Select(p => p.qty).FirstOrDefault().ToString()));
                entryContents.Add(new Cell(entryX, entryY+1, $"={Convert.ToChar(entryX + 64)}{entryY}/{Convert.ToChar(entryStartX + 64 + 6)}{entryY}"));
                entryX += 1;
                entryTitleX += 1;
                entryTitles.Add(new Cell(entryTitleX, entryTitleY, "返工数量") { Y2 = entryTitleY + 1 ,IsMerge=true});
                entryContents.Add(new Cell(entryX, entryY,
                    dataOtherGrouped.Where(p => p.month == i && p.testTimes == "4" ).Select(p => p.qty).FirstOrDefault().ToString()));
                entryContents.Add(new Cell(entryX, entryY+1, $"={Convert.ToChar(entryX + 64)}{entryY}/{Convert.ToChar(entryStartX + 64 + 6)}{entryY}"));
                entryX += 1;
                entryTitleX += 1;
                entryTitles.Add(new Cell(entryTitleX, entryTitleY, "订单内测试数量") { Y2 = entryTitleY + 1 ,IsMerge=true});
                entryContents.Add(new Cell(entryX, entryY, dataTestGrouped.Where(p => p.month == i).Sum(p => p.qty).ToString()));
                entryContents.Add(new Cell(entryX, entryY+1, $"={Convert.ToChar(entryX + 64)}{entryY}/{Convert.ToChar(entryStartX + 64 + 6)}{entryY}"));
                entryX += 1;
                entryTitleX += 1;
                entryTitles.Add(new Cell(entryTitleX, entryTitleY, "总产能") { Y2 = entryTitleY + 1 ,IsMerge=true});
                entryContents.Add(new Cell(entryX, entryY, dataTotalGrouped.Where(p => p.month == i).Sum(p => p.qty).ToString()));
                entryContents.Add(new Cell(entryX, entryY+1, $"={Convert.ToChar(entryX + 64)}{entryY}/{Convert.ToChar(entryStartX + 64 + 6)}{entryY}"));
                entryTitleX += 1;



                foreach (var cell in entryTitles)
                {
                    cell.BorderColor = Color.Black;
                    cell.IsBold = true;
                }


                foreach (var cell in titles)
                {
                    cell.FontSize = 18;
                    cell.IsMerge = true;
                }

                gatherCell.AddRange(entryTitles);
                gatherCell.AddRange(entryContents);
                gatherCell.AddRange(titles);
                foreach (var cell in gatherCell)
                {
                    cell.BorderColor = Color.Black;
                    cell.Width = 13.3m;
                }

                FillProduceSheetTool.PutCellIntoWorksheet(gatherCell, worksheet);
            }
        }
    }
}
